package com.yys.excel.yysexcel.utils.easyPoi.excelExample;

import cn.afterturn.easypoi.entity.ImageEntity;
import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.entity.TemplateExportParams;
import cn.hutool.core.date.DateUtil;
import cn.hutool.core.lang.Dict;
import cn.hutool.db.Entity;
import com.yys.excel.yysexcel.utils.AttachUtils;
import com.yys.excel.yysexcel.utils.dbUtil.DbUtil;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;

import java.io.FileOutputStream;
import java.util.List;

/**
 * 施工对比照
 */
public class SgdbzExcelPoi {
    public static void main(String[] args) {
        excute("");
    }

    public static void excute(String filePath) {
        List<Entity> data = getSgdb();
        int size = data.size();
        //模板文件路径
        TemplateExportParams params = new TemplateExportParams("templates/excel/template/施工对比照.xlsx");
        Dict dict = Dict.create();
        dict.set("date", DateUtil.now());

        dict.set("datas", data);

        Workbook workbook = ExcelExportUtil.exportExcel(params, dict);

        FileOutputStream fos = null;
        try {
            fos = new FileOutputStream(filePath);

            //横向 合并单元格
            int row = 13;
            // '修复后'
            for (int i = 0; i < size; i++) {
                //跳过第一个，因为第一个已经合并了，后面不会自动合并，所以需要手动合并
                if(i!=0){
                    row += 15;
                }
                //CellRangeAddress(第几行开始，第几行结束，第几列开始，第几列结束)
                workbook.getSheetAt(0).addMergedRegion(new CellRangeAddress(row, row, 0, 1));
                workbook.getSheetAt(0).addMergedRegion(new CellRangeAddress(row, row, 2, 3));
            }
            workbook.write(fos);
            fos.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public static List<Entity> getSgdb() {
        String sql = "USE ylt_101280101 SELECT d.DAMNUM,c.VALUE,d.DEVICENAME,'已修复' STATUSNAME,d.CLASSTYPE,dddu.BHMDS,d.DAMPOS,d.DAMDETAILS,dddu.NAME AS secondname,dddu.NAME thirdname,d.DETAILTYPENAME,dui.DISPLAYNAME REALNAME,CASE WHEN dccc.ID IS NOT NULL THEN fn.STATUSNAME ELSE '' END REMARK,drcc.name teamName,dmcc.repairtypename,d.CZNAME,d.COLORNAME,d.SPECOLOR,SUBSTRING (COMTIME,1,10) COMTIME,dmcc.QUANTITIES,dmcc.DAMDETAIL  ,atta.ccdz,atta.ccmc,atta.style,CASE WHEN dccc.ccdz IS NOT NULL THEN dccc.ccdz ELSE dmcc.ccdz END Comccdz,CASE WHEN dccc.ccmc IS NOT NULL THEN dccc.ccmc ELSE dmcc.ccmc END Comccmc,CASE WHEN dccc.style IS NOT NULL THEN dccc.style ELSE dmcc.style END Comstyle,dui.displayname,ofif.offices_code,dmcc.securitypencename,dmcc.securitypencecode,dmcc.securitypencelh FROM DAMAGE d LEFT JOIN officesinfo ofif ON ofif.id =d.added_office LEFT JOIN (SELECT dr.*FROM DAMAGERECEIVE dr,(SELECT pid,MAX (TIME) TIME FROM DAMAGERECEIVE GROUP BY pid) drc WHERE dr.PID=drc.PID AND dr.TIME=drc.time) drcc ON d.ID=drcc.PID LEFT JOIN (SELECT dm.*,atta_com.ccdz,atta_com.ccmc,atta_com.style FROM (SELECT pid,MAX (comtime) comtime FROM DAMAGECOMPLETE GROUP BY pid) dmc LEFT JOIN DAMAGECOMPLETE dm ON dm.pid=dmc.PID AND dm.COMTIME=dmc.comtime LEFT JOIN (\n" +
                "SELECT*FROM (\n" +
                "SELECT ROW_NUMBER () OVER (PARTITION BY t1.PID ORDER BY (SELECT SERVERPROPERTY('Collation'))) AS RNUM, *FROM ATTACH t1) AS T WHERE T.RNUM =1) atta_com ON atta_com.PID=dm.ID) dmcc ON d.ID=dmcc.PID LEFT JOIN (SELECT dc.*,atta_chk.ccdz,atta_chk.ccmc,atta_chk.style FROM (SELECT pid,MAX (checktime) checktime FROM DAMAGECHECK GROUP BY pid) dcc LEFT JOIN DAMAGECHECK dc ON dc.PID=dcc.PID AND dc.CHECKTIME=dcc.checktime LEFT JOIN (\n" +
                "SELECT*FROM (\n" +
                "SELECT ROW_NUMBER () OVER (PARTITION BY t1.PID ORDER BY (SELECT SERVERPROPERTY('Collation'))) AS RNUM, *FROM ATTACH t1) AS T WHERE T.RNUM =1) atta_chk ON atta_chk.PID=dc.ID) dccc ON d.ID=dccc.pid LEFT JOIN dfccode c ON c.TYPEID='1001' AND c.ISDELETE='0' AND d.location=c.NAME LEFT JOIN (SELECT ddd.name BHMDS,ddu.NAME ,ddd.CODE,ddu.QUANTIZE FROM dbo.DamDaily ddd LEFT JOIN DAMUNIT ddu ON ddu.CODE=ddd.UNIT) dddu ON dddu.CODE=d.DAMTYPE LEFT JOIN USER_INFO dui ON dui.ID =d.COLUSERID LEFT JOIN FLOW_NODE fn ON d.FLOWID=fn.ID LEFT JOIN USER_INFO ui ON ui.ID=d.COLUSERID LEFT JOIN (\n" +
                "SELECT*FROM (\n" +
                "SELECT ROW_NUMBER () OVER (PARTITION BY t1.PID ORDER BY (SELECT SERVERPROPERTY('Collation'))) AS RNUM, *FROM ATTACH t1) AS T WHERE T.RNUM =1) atta ON atta.PID=d.ID WHERE fn.STATUSCODE='4' AND d.DAMKINDS='B01' AND d.ISDELETE='0' AND d.DEVICETYPE='SS0' AND d.CLASSTYPECODE IN ('CCN2','CC4') ORDER BY d.SHOW_TOP DESC,d.COLTIME DESC";
        List<Entity> querys = DbUtil.querys(sql);
        int num = 1;
        for (Entity e : querys) {
            e.set("num","打印序号："+num).set("blank"," ");;
            byte[] firstImg = AttachUtils.getPicByte(e.getStr("ccdz") + "/" + e.getStr("ccmc"));
            ImageEntity imageFirst = new ImageEntity(firstImg,61,200);
            //设置列跨度
            imageFirst.setColspan(2);
            e.set("firstImg",imageFirst);

            byte[] lastImg = AttachUtils.getPicByte(e.getStr("Comccdz") + "/" + e.getStr("Comccmc"));
            ImageEntity imageLast = new ImageEntity(lastImg,61,200);
            imageLast.setColspan(2);
            e.set("lastImg",imageLast);

            //计算公式转换
            e.set("DAMDETAIL",toGetCalculationFormula(e.getStr("DAMDETAIL")));
            //安全围栏量化，加米
            e.set("securitypencelh",e.getStr("securitypencelh")+"米");
            //如果有合并的列，模板用 单引号 '' 常量  失效
//            e.set("xfq","修复前").set("xfh","修复后");;
            num++;
        }
        return querys;
    }

    /**
     * 得到计算公式
     *
     * @return {@link String}
     */
    public static String toGetCalculationFormula(String damdetail){
        if (damdetail.contains("长:")) {
            damdetail = damdetail.replace("长:","");
        }
        if (damdetail.contains("宽:")) {
            damdetail = damdetail.replace("宽:","");
        }
        if (damdetail.contains("高:")) {
            damdetail = damdetail.replace("高:","");
        }
        if (damdetail.contains("数量:")) {
            damdetail = damdetail.replace("数量:","");
        }
        if (damdetail.contains("~t")) {
            damdetail = damdetail.replace("~t","*");
            damdetail = damdetail.substring(0, damdetail.length() - 1);
        }
        return damdetail;
    }

}
